package cn.chinaflame.pagodas.utils.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.fileupload.FileItem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;

import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Maps;

import cn.chinaflame.pagodas.entity.PaProductPosition;


public final class ExcelReaderUtil {
	public ExcelReaderUtil() {
	}

	private static final Logger logger = LoggerFactory.getLogger(ExcelReaderUtil.class);
	// excel2003扩展名
	public static final String EXCEL03_EXTENSION = ".xls";
	// excel2007扩展名
	public static final String EXCEL07_EXTENSION = ".xlsx";
	//总行数
    private int totalRows = 0;  
    //总条数
    private int totalCells = 0; 
    //错误信息接收器
    private static String errorMsg;
    //获取总行数
    public int getTotalRows()  { return totalRows;} 
    //获取总列数
    public int getTotalCells() {  return totalCells;} 
    //获取错误信息
    public String getErrorInfo() { return errorMsg; }  
    
	/**
	 * 读取Excel文件，可能是03也可能是07版本
	 * 
	 * @param reader
	 * @param fileName
	 * @throws Exception
	 */
	public static void readExcel(IRowReader reader, String fileName) throws Exception {
		// 处理excel2003文件
		if (fileName.endsWith(EXCEL03_EXTENSION)) {
			Excel2003Reader excel03 = new Excel2003Reader(reader);
			excel03.process(fileName);
			// 处理excel2007文件
		} else if (fileName.endsWith(EXCEL07_EXTENSION)) {
			Excel2007Reader excel07 = new Excel2007Reader(reader);
			excel07.process(fileName);
		} else {
			throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
		}
	}

	/**
	 * 读取Excel文件，可能是03也可能是07版本
	 * 
	 * @param reader
	 * @param fileName
	 * @param inputStream
	 * @throws Exception
	 */
	public static void readExcel(IRowReader reader, String fileName, InputStream inputStream) throws Exception {
		// 处理excel2003文件
		if (fileName.endsWith(EXCEL03_EXTENSION)) {
			Excel2003Reader excel03 = new Excel2003Reader(reader);
			excel03.process(inputStream);
			// 处理excel2007文件
		} else if (fileName.endsWith(EXCEL07_EXTENSION)) {
			Excel2007Reader excel07 = new Excel2007Reader(reader);
			excel07.process(inputStream);
		} else {
			throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
		}
	}

	/**
	 * 获取Excel数据,返回List<String[]>
	 * 
	 * @param sheetNumber
	 *            读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final List<String[]> excelToArrayList(String filePath, int... sheetNumber) throws Exception {
		List<String[]> resultList = null;
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			resultList = excelToArrayList(filePath, is, sheetNumber);
		} catch (Exception e) {
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return resultList;
	}

	/**
	 * 获取Excel数据,返回List<String[]>
	 * 
	 * @param sheetNumber
	 *            读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final List<String[]> excelToArrayList(String fileName, FileItem fileItem, int... sheetNumber)
			throws Exception {
		List<String[]> resultList = null;
		InputStream is = null;
		try {
			is = fileItem.getInputStream();
			resultList = excelToArrayList(fileName, is, sheetNumber);
		} catch (Exception e) {
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return resultList;
	}

	/**
	 * 获取Excel数据,返回List<String[]>;create
	 * 
	 * @param sheetNumber
	 *            读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final List<String[]> excelToArrayList(String fileName, InputStream is, final int... sheetNumber)
			throws Exception {
		final ArrayList<String[]> resultList = new ArrayList<String[]>();
		try {
			readExcel(new IRowReader() {
				public void getRows(int sheetIndex, int curRow, List<String> rowlist) {
					if (sheetNumber == null) {
						resultList.add(rowlist.toArray(new String[] {}));
					} else {
						for (int k = 0; k < sheetNumber.length; k++) {// 循环工作表
							if (sheetIndex == sheetNumber[k]) {
								resultList.add(rowlist.toArray(new String[] {}));
							}
						}
					}
				}
			}, fileName, is);
		} catch (Exception e) {
			logger.error("Read excel error.", e);
			throw e;
		}
		return resultList;
	}
	
  /**
   * 读EXCEL文件，获取信息集合
   * @param fielName
   * @return
   */
//	public Map<String,List<PaProductPosition>> getExcelInfo(MultipartFile mFile) {
//	
//	}
  
  /**
   * 根据excel里面的内容读取客户信息
   * @param is 输入流
   * @param isExcel2003 excel是2003还是2007版本
   * @return
   * @throws IOException
   */
	public List<PaProductPosition> createExcel(InputStream is, boolean isExcel2003) {
		List<PaProductPosition> list = null;
		try{
			Workbook wb = null;
			if (isExcel2003) {// 当excel是2003时,创建excel2003
				wb = new HSSFWorkbook(is);
			} else {// 当excel是2007时,创建excel2007
				wb = new XSSFWorkbook(is);
			}
			list = readExcelValue(wb);// 读取Excel里面客户的信息
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}
  
  /**
   * 读取Excel里面客户的信息
   * @param wb
   * @return
   */
	private List<PaProductPosition> readExcelValue(Workbook wb) {
		// 得到第一个shell
		Sheet sheet = wb.getSheetAt(0);
		// 得到Excel的行数
		this.totalRows = sheet.getPhysicalNumberOfRows();
		// 得到Excel的列数(前提是有行数)
		if (totalRows > 1 && sheet.getRow(0) != null) {
			this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}
		List<PaProductPosition> list = new ArrayList<PaProductPosition>();
		// 循环Excel行数
		for (int r = 1; r < totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null){
				continue;
			}
			PaProductPosition paproductposition = new PaProductPosition();
			// 循环Excel的列
			for (int c = 0; c < this.totalCells; c++) {
				Cell cell = row.getCell(c);
				if (null != cell) {
					if (c == 0) {
						//如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
							String name = String.valueOf(cell.getNumericCellValue());
							paproductposition.setName(name.substring(0, name.length()-2>0?name.length()-2:1));//名称
						}else{
							paproductposition.setName(cell.getStringCellValue());//名称
						}
					} else if (c == 1) {
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
							String sex = String.valueOf(cell.getNumericCellValue());
							//PaProductPosition.setSex(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//性别
						}else{
							//PaProductPosition.setSex(cell.getStringCellValue());//性别
						}
					} else if (c == 2){
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
							String age = String.valueOf(cell.getNumericCellValue());
							//PaProductPosition.setAge(age.substring(0, age.length()-2>0?age.length()-2:1));//年龄
						}else{
							//PaProductPosition.setAge(cell.getStringCellValue());//年龄
						}
					}
				}
			}
			// 添加到list
			list.add(paproductposition);
		}
		return list;
	}
	
	/**
	 * 验证EXCEL文件
	 * 
	 * @param filePath
	 * @return
	 */
	public static boolean validateExcel(String filePath) {
		if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
			errorMsg = "文件名不是excel格式";
			return false;
		}
		return true;
	}
	
	// @描述：是否是2003的excel，返回true是2003 
    public static boolean isExcel2003(String filePath)  {  
         return filePath.matches("^.+\\.(?i)(xls)$");  
     }  
   
    //@描述：是否是2007的excel，返回true是2007 
    public static boolean isExcel2007(String filePath)  {  
         return filePath.matches("^.+\\.(?i)(xlsx)$");  
     }  

}
